set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_whole_effect_deliver_summary_dt partition(dt)
select
      wide.sign_date           --签收日期
     ,wide.sign_network_code   --签收网点编码
     ,wide.sign_network_name   --签收网点名称
     ,wide.sign_fran_code      --签收加盟商code
     ,wide.sign_fran_name      --签收加盟商名称
     ,wide.sign_agent_code     --签收代理区code
     ,wide.sign_agent_name     --签收代理区名称
     ,wide.sign_regional_id    --签收大区id
     ,wide.sign_regional_desc  --签收大区名称
     ,wide.sign_area_id        --签收区县id
     ,wide.sign_area_desc      --签收区县名称
     ,wide.sign_city_id        --签收城市id
     ,wide.sign_city_desc      --签收城市名称
     ,wide.sign_provider_id    --签收省份id
     ,wide.sign_provider_desc  --签收省份名称
     ,wide.ordersource_code    --订单来源编码
     ,wide.ordersource_name    --订单来源名称
     ,wide.deliver_user_code   --出仓员编码
     ,wide.deliver_user_name   --出仓员名称
     ,round(sum(wide.real_pre_sign_difftime               ),2) as actual_aging_sign_time
     ,round(sum(wide.end_network_arrival_deliver_difftime ),2) as end_network_arrival_deliver_time
     ,round(sum(wide.deliver_aging_sign_difftime          ),2) as deliver_aging_sign_time
     ,sum(case when wide.real_pre_sign_difftime              >0 then 1 else 0 end) as actual_aging_sign_cnt
     ,sum(case when wide.end_network_arrival_deliver_difftime>0 then 1 else 0 end) as end_network_arrival_deliver_cnt
     ,sum(case when wide.deliver_aging_sign_difftime         >0 then 1 else 0 end) as deliver_aging_sign_cnt
     ,sum(case when wide.deliver_aging_sign_difftime > 24 then 1 else 0 end) as if_deliver_24h_cnt  --派件-时效签收超24H量
     ,wide.if_reback              as if_reback        --是否退转件,1:是,0:否
     ,wide.if_end_piece           as if_end_piece     --是否完结件,1:是,0:否
     ,wide.if_intercept           as if_intercept     --是否拦截件,1:是,0:否
     ,dim.area_code               as sign_zone_code   --片区编码
     ,dim.area_name               as sign_zone_name   --片区名称
     ,wide.sign_virt_code         as sign_virt_code   --虚拟代理区编码
     ,wide.sign_virt_name         as sign_virt_name   --虚拟代理区名称
     ,dim_manager.network_manager as sign_network_manager_name  --网管名称
     ,count(case when wide.deliver_scan_time is not null then waybill_no end) as deliver_cnt  --派件量
     ,count(case when wide.if_arrival_deliver = 1 then waybill_no end) as arrival_deliver_cnt --到出一体量
     ,dim_qxt.times             as qxt_deliver_aging_sign_time
     ,wide.if_deliver_township  as if_deliver_township
     ,split(dim.area_staff_name,'/')[0] as zone_staff_name
     ,wide.if_after_deliver as if_after_deliver  --是否稍后出仓
     ,dim.zone_code as sign_area_zone_code --区域片区编码
     ,dim.zone_name as sign_area_zone_name --区域片区名称
     ,wide.sign_date as dt --签收日期
from jms_dws.dws_wide_unsign_whole_effect_dt wide
left join jms_dim.dim_network_manager_base dim_manager --网管维度表
    on dim_manager.network_code = wide.sign_network_code
left join jms_dim.dim_network_whole_massage dim
    on dim.code = wide.sign_network_code
left join jms_dim.dim_whole_province_configuration_base dim_qxt
    on dim_qxt.agent_code = wide.sign_agent_code
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and wide.send_network_code is not null
  and wide.sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
group by wide.sign_date                  --签收日期
        ,wide.sign_network_code          --签收网点编码
        ,wide.sign_network_name          --签收网点名称
        ,wide.sign_fran_code             --签收加盟商code
        ,wide.sign_fran_name             --签收加盟商名称
        ,wide.sign_agent_code            --签收代理区code
        ,wide.sign_agent_name            --签收代理区名称
        ,wide.sign_regional_id           --签收大区id
        ,wide.sign_regional_desc         --签收大区名称
        ,wide.sign_area_id               --签收区县id
        ,wide.sign_area_desc             --签收区县名称
        ,wide.sign_city_id               --签收城市id
        ,wide.sign_city_desc             --签收城市名称
        ,wide.sign_provider_id           --签收省份id
        ,wide.sign_provider_desc         --签收省份名称
        ,wide.ordersource_code           --订单来源编码
        ,wide.ordersource_name           --订单来源名称
        ,wide.deliver_user_code          --出仓员编码
        ,wide.deliver_user_name          --出仓员名称
        ,wide.if_reback                  --是否退转件,1:是,0:否
        ,wide.if_end_piece               --是否完结件,1:是,0:否
        ,wide.if_intercept               --是否拦截件,1:是,0:否
        ,dim.area_code                   --片区编码
        ,dim.area_name                   --片区名称
        ,wide.sign_virt_code             --虚拟代理区编码
        ,wide.sign_virt_name             --虚拟代理区名称
        ,dim_manager.network_manager     --网管名称
        ,dim_qxt.times                   --七星潭派签时长
        ,wide.if_deliver_township        --是否出仓乡镇
        ,split(dim.area_staff_name,'/')[0]
        ,wide.if_after_deliver 
        ,dim.zone_code 
        ,dim.zone_name 
distribute by wide.sign_date,pmod(hash(rand()),10)
;